﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace LibMS.Models
{
    public class DapperSqlHelper<T> : IDisposable where T : class
    {
        protected IDbConnection _dbConnection;

        public DapperSqlHelper(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        public DapperSqlHelper()
        {
            _dbConnection = MSSqlConnection.GetConnection();
        }

        public virtual IEnumerable<T> QuerySp(out int? total, out string sqlMessage, string spName, DynamicParameters parameters)
        {
            try
            {
                if (parameters == null) parameters = new DynamicParameters();
                parameters.Add("@Total", dbType: System.Data.DbType.Int32, direction: System.Data.ParameterDirection.Output);
                OpenConnection();
                var result = _dbConnection.Query<T>(spName, parameters, commandType: CommandType.StoredProcedure);
                total = parameters.Get<int?>("@Total");
                sqlMessage = null;
                return result;
            }
            catch (Exception e)
            {
                total = null;
                sqlMessage = e.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public virtual IEnumerable<T> QuerySpNT(out string sqlMessage, string spName, DynamicParameters parameters)
        {
            try
            {
                OpenConnection();
                var result = _dbConnection.Query<T>(spName, parameters, commandType: CommandType.StoredProcedure);
                sqlMessage = null;
                return result;
            }
            catch (Exception e)
            {
                sqlMessage = e.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public virtual T QuerySp(out string sqlMessage, string spName, DynamicParameters parameters)
        {
            try
            {
                OpenConnection();
                var result = _dbConnection.Query<T>(spName, parameters, commandType: CommandType.StoredProcedure);
                sqlMessage = null;
                if (result.Count() == 0)
                {
                    return null;
                }
                return result.First();
            }
            catch (Exception e)
            {
                sqlMessage = e.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public virtual int? ExecuteSp(out string sqlMessage, string spName, DynamicParameters parameters)
        {
            try
            {
                OpenConnection();
                var result = _dbConnection.Execute(spName, parameters, commandType: CommandType.StoredProcedure);
                sqlMessage = null;
                return result;
            }
            catch (Exception e)
            {
                sqlMessage = e.Message;
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        protected bool OpenConnection()
        {
            try
            {
                if (_dbConnection != null && _dbConnection.State != ConnectionState.Open)
                {
                    _dbConnection.Open();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        protected bool CloseConnection()
        {
            try
            {
                if (_dbConnection != null && _dbConnection.State != ConnectionState.Closed)
                {
                    _dbConnection.Close();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public void Dispose()
        {
            throw new NotImplementedException();
        }
    }
}